using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class EmployeeDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procEmployee_add]";
        private string _strSPUpdateName = "dbo.[procEmployee_update]";
        private string _strSPDeleteName = "dbo.[procEmployee_delete]";
        private string _strSPGetName = "dbo.[procEmployee_get]";
        private string _strSPGetAllName = "dbo.[procEmployee_getall]";
        private string _strSPGetPages = "dbo.[procEmployee_getpaged]";
        private string _strSPIsExist = "dbo.[procEmployee_isexist]";
        private string _strTableName = "Employee";
        #endregion Local Variable

        #region Method
        public EmployeeInfo Get_Employee(
            String EmployeeCode
        , ref string sErr)
        {
            EmployeeInfo objEntr = new EmployeeInfo();
            InitConnect();
            InitSPCommand(_strSPGetName);
            AddParameter("EmployeeCode", EmployeeCode);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}

            if (list.Rows.Count > 0)
                objEntr = (EmployeeInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            EmployeeInfo result = new EmployeeInfo();
            result.EmployeeCode = (dt.Rows[i]["EmployeeCode"] == DBNull.Value ? "" : (String)dt.Rows[i]["EmployeeCode"]);
            result.EmployeeName = (dt.Rows[i]["EmployeeName"] == DBNull.Value ? "" : (String)dt.Rows[i]["EmployeeName"]);

            return result;
        }

        public DataTable GetAll_Employee(ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetAllName);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add_Employee(EmployeeInfo objEntr, ref string sErr)
        {
            int ret = -1;
            openConnection();
            InitCommand(@"INSERT INTO Employee(EmployeeCode, EmployeeName)
                VALUES     (@EmployeeCode,@EmployeeName)", CommandType.Text);

            AddParameter("EmployeeCode", objEntr.EmployeeCode);
            AddParameter("EmployeeName", objEntr.EmployeeName);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();

                if (tmp != null && tmp != DBNull.Value) ret = Convert.ToInt32(tmp);
                else ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update_Employee(EmployeeInfo objEntr)
        {

            openConnection();
            InitSPCommand(_strSPUpdateName);

            AddParameter("EmployeeCode", objEntr.EmployeeCode);
            AddParameter("EmployeeName", objEntr.EmployeeName);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete_Employee(
            String EmployeeCode = null
        )
        {
            openConnection();
            string sql = @" DELETE FROM Employee ";
            if (EmployeeCode != null) sql += "WHERE EmployeeCode=@EmployeeCode";
            InitCommand(sql, CommandType.Text);
            if (EmployeeCode != null) AddParameter("EmployeeCode", EmployeeCode);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

       

        public Boolean IsExist_Employee(
            String EmployeeCode
        )
        {
            InitConnect();
            InitSPCommand(_strSPIsExist);

            AddParameter("EmployeeCode", EmployeeCode);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(EmployeeInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            openConnection();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally {closeConnection();}
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

    }
}
